﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using ColdSun.Commons.Configs;
using ColdSun.Commons.Entities;
using ColdSun.Commons;
using System.Reflection;

using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Word;
using System.Runtime.InteropServices;
using ColdSun.Framework;

namespace ColdSun.Services
{
    public class StatementCreditService
    {
        public string GenerateStatementCredit(string originalFile, string followingFile, DateTime reportDate, string reportDays, string[] reportDeparments, string exportFolder)
        {
            Worksheet workingWorksheet = null;
            Worksheet followWorksheet = null;
            Worksheet exportWorksheet = null;
            Workbook followWorkbook = null;
            Workbook exportWorkbook = null;
            Workbook workingWorkbook = null;
            Microsoft.Office.Interop.Excel.Application followExcelApplication = null;
            Microsoft.Office.Interop.Excel.Application exportExcelApplication = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(originalFile))
                {
                    Logger.Info(string.Format("Can't find the original file", originalFile));
                    return string.Format("Can't find the original file", originalFile);
                }

                excelApplication = new Microsoft.Office.Interop.Excel.Application();
                workingWorkbook = excelApplication.Workbooks.Open(originalFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                workingWorksheet = (Worksheet)workingWorkbook.Worksheets.get_Item(1);
                Logger.Info(string.Format("Process original sheet {0}", workingWorkbook.Name));

                string[] deleteColumns = CommonConfigs.DeleteOriginalColumnIndexs.Split(',');
                foreach (string columnIndex in deleteColumns)
                {
                    ((Microsoft.Office.Interop.Excel.Range)workingWorksheet.Range[Utilities.Cell(columnIndex, 1), Utilities.Cell(columnIndex, 1)]).EntireColumn.Delete(null);
                }

                string[] insertColumns = CommonConfigs.InsertOriginalColumnIndexs.Split(',');
                foreach (string columnIndex in insertColumns)
                {
                    ((Microsoft.Office.Interop.Excel.Range)workingWorksheet.Range[Utilities.Cell(columnIndex, 1), Utilities.Cell(columnIndex, 1)]).EntireColumn.Insert(XlInsertShiftDirection.xlShiftToRight, false);
                }

                //Copy Sheet from following file
                followWorkbook = excelApplication.Workbooks.Open(followingFile);
                followWorksheet = (Worksheet)followWorkbook.Worksheets.get_Item(1);
                followWorksheet.Copy(Missing.Value, workingWorksheet);
                Logger.Info(string.Format("Process following sheet {0}", workingWorkbook.Name));
                //followWorkbook.Save();

                string exportExcelTemplateFile = Utilities.GetFullFileName(CommonConfigs.ExportTemplateFile);
                exportWorkbook = excelApplication.Workbooks.Open(exportExcelTemplateFile);
                Worksheet exportTemplateWorksheet = (Worksheet)exportWorkbook.Worksheets.get_Item(1);

                //report sheet
                exportWorksheet = (Worksheet)workingWorkbook.Worksheets.Add(Missing.Value, (Worksheet)followWorkbook.Worksheets.get_Item(2));
                exportWorksheet.Name = "Export";

                string[] exportColumnWidths = CommonConfigs.ExportColumnWidths.Split(',');
                foreach (string columnWidth in exportColumnWidths)
                {
                    string[] mapping = columnWidth.Split(':');
                    SetColumnWidth(exportWorksheet, mapping[0].Trim().ToUpper(), Convert.ToDecimal(mapping[1]));
                }
                int headerExportIndex = 1;
                string[] exportColumnIndexs = CommonConfigs.ExportColumnIndexs.Split(',');
                foreach (string columnIndex in exportColumnIndexs)
                {
                    CopyValueAndFormat(exportTemplateWorksheet, columnIndex.Trim().ToUpper(), headerExportIndex, exportWorksheet, columnIndex.Trim().ToUpper(), headerExportIndex);
                }

                //Add custom edit
                exportWorksheet = (Worksheet)workingWorkbook.Worksheets.get_Item(3);
                int rowIndex = CommonConfigs.OriginalHeaderRowIndex + 1;
                int reportRowIndex = 2;
                List<Department> departments = new List<Department>();
                bool isExportCurrentDepartment = false;
                string[] reportDayList = reportDays.Split(',');
                string[] reportAccountTypes = CommonConfigs.ExportAccountTypes.Split(',');
                while (true)
                {
                    string departmentCode = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("B", rowIndex)).Value);
                    string nextDepartmentCode = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("B", rowIndex + 1)).Value);
                    string firstCIF = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("D", rowIndex + 1)).Value);
                    if (string.IsNullOrEmpty(departmentCode) && string.IsNullOrEmpty(nextDepartmentCode) && string.IsNullOrEmpty(firstCIF))
                    {
                        break;// end of file
                    }
                    if (!string.IsNullOrEmpty(departmentCode)) //  detail of department
                    {
                        if (isExportCurrentDepartment && departments.Count > 0 &&
                            departments[departments.Count - 1].Accounts != null
                            && departments[departments.Count - 1].Accounts.Count > 0)
                        {
                            this.GenerateToWordFile(exportFolder, departments[departments.Count - 1], reportDate);
                        }

                        departments.Add(new Department());
                        departments[departments.Count - 1].DepartmentCode = departmentCode;// Utilities.PaddingValue(departmentCode, '0', 3, true);
                        departments[departments.Count - 1].Accounts = new List<Account>();
                        if (reportDeparments == null || Utilities.IsIncludeString(departmentCode, reportDeparments))
                        {
                            isExportCurrentDepartment = true;
                        }
                        else
                        {
                            isExportCurrentDepartment = false;
                        }
                    }
                    else if (isExportCurrentDepartment && string.IsNullOrEmpty(departmentCode)) // detail of account
                    {
                        string accountType = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("N", rowIndex)).Value);
                        if (Utilities.IsIncludeString(accountType, reportAccountTypes))
                        {
                            string startDate = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("K", rowIndex)).Value);
                            bool isExportDay = false;
                            foreach (string reportDay in reportDayList)
                            {
                                if (startDate.StartsWith(reportDay))
                                {
                                    isExportDay = true;
                                    break;
                                }
                            }
                            if (isExportDay)
                            {
                                workingWorksheet.get_Range(Utilities.Cell("Q", rowIndex)).Value = string.Format(CommonConfigs.VLOOKUPData, rowIndex);
                                decimal laiThang;
                                decimal.TryParse(string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("Q", rowIndex)).Value), out laiThang);

                                if (laiThang <= 0)
                                {
                                    rowIndex++;
                                    continue;
                                }

                                workingWorksheet.get_Range(Utilities.Cell("L", rowIndex)).Value = string.Format("=LEFT(K{0},2)", rowIndex);

                                Account currentAccount = new Account();
                                currentAccount.CIF = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("D", rowIndex)).Value);
                                currentAccount.AccountName = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("E", rowIndex)).Value);
                                currentAccount.AccountID = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("F", rowIndex)).Value);
                                currentAccount.ContractID = string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("G", rowIndex)).Value);

                                currentAccount.LaiThang = laiThang;
                                decimal laiNgay;
                                decimal.TryParse(string.Format("{0}", workingWorksheet.get_Range(Utilities.Cell("P", rowIndex)).Value), out laiNgay);
                                currentAccount.LaiNgay = laiNgay;
                                departments[departments.Count - 1].Accounts.Add(currentAccount);

                                foreach (string columnIndex in exportColumnIndexs)
                                {
                                    CopyFormat(exportTemplateWorksheet, columnIndex.Trim().ToUpper(), headerExportIndex + 1, exportWorksheet, columnIndex.Trim().ToUpper(), reportRowIndex);
                                }
                                CopyValue(workingWorksheet, "D", rowIndex, exportWorksheet, "A", reportRowIndex);
                                CopyValue(workingWorksheet, "E", rowIndex, exportWorksheet, "B", reportRowIndex);
                                CopyValue(workingWorksheet, "F", rowIndex, exportWorksheet, "C", reportRowIndex);
                                CopyValue(workingWorksheet, "Q", rowIndex, exportWorksheet, "D", reportRowIndex);
                                CopyValue(workingWorksheet, "P", rowIndex, exportWorksheet, "E", reportRowIndex);
                                SetValue(exportWorksheet, "F", reportRowIndex, string.Format("=IF(D{0}>E{0}, D{0} - E{0}, \"\")", reportRowIndex));
                                SetValue(exportWorksheet, "G", reportRowIndex, string.Format("=IF(D{0}<E{0}, E{0} - D{0}, \"\")", reportRowIndex));
                                reportRowIndex++;
                            }
                        }
                    }

                    rowIndex++;
                }

                string exportTempFile = string.Format("{0}\\{1}", exportFolder, CommonConfigs.ExportExcelTempFileName)
                    .Replace("#Date#", reportDate.ToString(CommonConfigs.ExportDateTimeFormatInFileName));
                workingWorkbook.SaveAs(exportTempFile);
                Logger.Info(string.Format("Save excel as to file {0}", exportTempFile));
                return "Successful";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(workingWorksheet);
                ReleaseComObjectService.ReleaseComObject(followWorksheet);
                ReleaseComObjectService.ReleaseComObject(exportWorksheet);

                ReleaseComObjectService.ReleaseWorkbookObject(workingWorkbook);
                ReleaseComObjectService.ReleaseWorkbookObject(followWorkbook, false);
                ReleaseComObjectService.ReleaseWorkbookObject(exportWorkbook);

                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);
                ReleaseComObjectService.ReleaseExcelApplicationObject(followExcelApplication);
                ReleaseComObjectService.ReleaseExcelApplicationObject(exportExcelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        public string GenerateToWordFile(string exportFolder, Department currentDepartment, DateTime reportDate)
        {
            string result = "";
            List<Department> departments = DepartmentService.GetInstance().GetDepartments(currentDepartment.DepartmentCode);
            if (departments == null || departments.Count == 0)
            {
                return GenerateToFile(exportFolder, currentDepartment, reportDate);
            }
            if (departments.Count == 1)
            {
                departments[0].Accounts = currentDepartment.Accounts;
                return GenerateToFile(exportFolder, departments[0], reportDate);
            }
            //if have more than 1 deparments have the same department code.
            int currentIndex = 0;
            foreach (Department item in departments)
            {
                item.Accounts = new List<Account>();
                foreach (Account accountItem in currentDepartment.Accounts)
                {
                    if (accountItem.ContractID.ToUpper().Trim().Contains(item.ContractCode.Trim().ToUpper()))
                    {
                        item.Accounts.Add(accountItem);
                    }
                    else if (currentIndex == 0) // If the first branch, this one can include the incorrect Contract ID
                    {
                        bool isMissingContract = true;
                        foreach (Department checkItem in departments)
                        {
                            if (accountItem.ContractID.ToUpper().Trim().Contains(checkItem.ContractCode.Trim().ToUpper()))
                            {
                                isMissingContract = false;
                                break;
                            }
                        }
                        if (isMissingContract)
                        {
                            item.Accounts.Add(accountItem);
                        }
                    }
                }
                currentIndex++;
                if (item.Accounts.Count > 0)
                {
                    result += GenerateToFile(exportFolder, item, reportDate);
                }
            }
            return result;
        }

        public string GenerateToFile(string exportFolder, Department currentDepartment, DateTime reportDate)
        {
            Microsoft.Office.Interop.Word.Application wordObj = null;
            Microsoft.Office.Interop.Excel.Application exportExcelApplication = null;
            Microsoft.Office.Interop.Excel.Workbook exportWorkBook = null;
            Worksheet exportWorksheet = null;
            Sheets exportSheets = null;
            Document templateDoc = null;
            try
            {
                object exportWordTemplateFile = Utilities.GetFullFileName(CommonConfigs.ExportWordTemplateFile);
                if (!File.Exists(exportWordTemplateFile.ToString()))
                {
                    Logger.Info(string.Format("Can't find the Word Template file", exportWordTemplateFile));
                    return string.Format("Can't find the Word Template file", exportWordTemplateFile);
                }

                string excelFile = Utilities.GetFullFileName(CommonConfigs.ExportExcelTemplateFile);
                exportExcelApplication = new Microsoft.Office.Interop.Excel.Application();
                exportWorkBook = exportExcelApplication.Workbooks.Open(excelFile);
                exportSheets = exportWorkBook.Worksheets;
                exportWorksheet = (Worksheet)exportSheets.get_Item(1);


                //copy header
                int headerExportIndex = 1;
                exportWorksheet.get_Range(Utilities.Cell("E", headerExportIndex)).Value = exportWorksheet.get_Range(Utilities.Cell("E", headerExportIndex)).Value.ToString().Replace("#Date#", reportDate.ToString("dd/MM/yyyy"));

                string[] exportColumnIndexs = CommonConfigs.ExportColumnIndexs.Split(',');

                // add detail
                for (int index = 0; index < currentDepartment.Accounts.Count; index++)
                {
                    int rowIndex = headerExportIndex + index + 1;
                    Account currentAccount = currentDepartment.Accounts[index];

                    foreach (string columnIndex in exportColumnIndexs)
                    {
                        CopyFormat(exportWorksheet, columnIndex.Trim().ToUpper(), headerExportIndex + 1, exportWorksheet, columnIndex.Trim().ToUpper(), rowIndex);
                    }
                    SetValue(exportWorksheet, "A", rowIndex, string.Format("{0}", currentAccount.CIF));
                    SetValue(exportWorksheet, "B", rowIndex, string.Format("{0}", currentAccount.AccountName));
                    SetValue(exportWorksheet, "C", rowIndex, string.Format("{0}", currentAccount.AccountID));
                    SetValue(exportWorksheet, "D", rowIndex, string.Format("{0}", currentAccount.LaiThang));
                    SetValue(exportWorksheet, "E", rowIndex, string.Format("{0}", currentAccount.LaiNgay));
                    if (currentAccount.LaiThang > currentAccount.LaiNgay)
                    {
                        SetValue(exportWorksheet, "F", rowIndex, string.Format("{0}", currentAccount.LaiThang - currentAccount.LaiNgay));
                    }
                    if (currentAccount.LaiThang < currentAccount.LaiNgay)
                    {
                        SetValue(exportWorksheet, "G", rowIndex, string.Format("{0}", currentAccount.LaiNgay - currentAccount.LaiThang));
                    }
                }

                object missing = Missing.Value;
                wordObj = new Microsoft.Office.Interop.Word.Application();
                wordObj.Visible = false;

                templateDoc = new Document();
                templateDoc = wordObj.Documents.Open(ref exportWordTemplateFile);
                object replaceAll = Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll;

                string saveFileName = string.Format("{0}\\{1}", exportFolder, CommonConfigs.ExportWordFileName)
                    .Replace("#Name#", currentDepartment.DepartmentName)
                    .Replace("#ID#", currentDepartment.DepartmentCode)
                    .Replace("#Date#", reportDate.ToString(CommonConfigs.ExportDateTimeFormatInFileName));
                object saveWordFileName = saveFileName;
                templateDoc.SaveAs(ref saveWordFileName);
                Logger.Info(string.Format("Save word as to file {0} for department {1}", saveWordFileName, currentDepartment.DepartmentCode));

                exportWorksheet.Range[string.Format("A1:G{0}", currentDepartment.Accounts.Count + 1)].Copy();
                Microsoft.Office.Interop.Word.Find findObject = wordObj.Selection.Find;

                findObject.Text = "#Department#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = currentDepartment.DepartmentFullName;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "#Table#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Execute();
                findObject.Replacement.Application.Selection.Paste();

                findObject.Text = "#Table#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = ""; ;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "#Place#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = currentDepartment.DepartmentPlace;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "#dd#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = reportDate.ToString("dd");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "#MM#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = reportDate.ToString("MM");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "#yyyy#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = reportDate.ToString("yyyy");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                if (CommonConfigs.ExportExcelIsSave)
                {
                    string saveExcelFileName = string.Format("{0}\\{1}", exportFolder, CommonConfigs.ExportExcelFileName)
                        .Replace("#Name#", currentDepartment.DepartmentName)
                        .Replace("#ID#", currentDepartment.DepartmentCode)
                        .Replace("#Date#", reportDate.ToString(CommonConfigs.ExportDateTimeFormatInFileName));
                    exportWorkBook.SaveAs(saveExcelFileName);
                }
                templateDoc.Save();
                exportWorksheet.Range["A1"].Copy();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                if (templateDoc != null)
                {
                    templateDoc.Close();
                }

                if (wordObj != null)
                {
                    wordObj.Quit();
                    ReleaseComObjectService.ReleaseComObject(wordObj);
                }

                ReleaseComObjectService.ReleaseComObject(exportWorksheet);
                ReleaseComObjectService.ReleaseComObject(exportSheets);
                ReleaseComObjectService.ReleaseWorkbookObject(exportWorkBook, false);
                ReleaseComObjectService.ReleaseExcelApplicationObject(exportExcelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return "Successful";
        }

        #region Utilities
        private void SetValue(Worksheet workingWorksheet, string columnName, int rowIndex, string value)
        {
            workingWorksheet.get_Range(Utilities.Cell(columnName, rowIndex)).Value = value;
        }

        private void CopyValue(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex, Worksheet destWorksheet, string destColumnName, int destRowIndex)
        {
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value;
        }

        private void CopyValueAndFormat(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex, Worksheet destWorksheet, string destColumnName, int destRowIndex)
        {
            Microsoft.Office.Interop.Excel.Range sourceRange = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex));
            sourceRange.Copy();
            Microsoft.Office.Interop.Excel.Range destRange = destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex));
            destRange.PasteSpecial(
                    Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,
                    Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                    Type.Missing,
                    Type.Missing);
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value;
        }

        private void CopyFormat(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex, Worksheet destWorksheet, string destColumnName, int destRowIndex)
        {
            Microsoft.Office.Interop.Excel.Range sourceRange = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex));
            sourceRange.Copy();
            Microsoft.Office.Interop.Excel.Range destRange = destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex));
            destRange.PasteSpecial(
                    Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll,
                    Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                    Type.Missing,
                    Type.Missing);
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = "";
        }

        private void SetColumnWidth(Worksheet workingWordsheet, string columnName, decimal columnWidth)
        {
            workingWordsheet.get_Range(string.Format("{0}:{0}", columnName), System.Type.Missing).EntireColumn.ColumnWidth = columnWidth;
        }
        #endregion Utilities

    }
}
